from typing import Optional

from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.ext.asyncio import async_sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.asyncio import AsyncAttrs
from sqlalchemy.orm import DeclarativeBase
from urllib.parse import quote_plus
from config.env import DataBaseConfig as dbcfg, AppConfig
from urllib.parse import quote_plus as urlquote
from utils.log_util import logger as log
import re
from sqlalchemy.ext.declarative import declarative_base, declared_attr



echo_enable = True if AppConfig.app_env != 'prod' else False
if dbcfg.db_type == 'postgresql':
    ASYNC_SQLALCHEMY_DATABASE_URL = (
        f'postgresql+asyncpg://{dbcfg.db_username}:{quote_plus(dbcfg.db_password)}@'
        f'{dbcfg.db_host}:{dbcfg.db_port}/{dbcfg.db_database}'
    )
    log.info('postgresql is running!')
    DATABASE_URL = f"postgresql://{dbcfg.db_username}:{urlquote(dbcfg.db_password)}@{dbcfg.db_host}:{dbcfg.db_port}/{dbcfg.db_database}"
else:
    ASYNC_SQLALCHEMY_DATABASE_URL = (
        f'mysql+asyncmy://{dbcfg.db_username}:{quote_plus(dbcfg.db_password)}@'
        f'{dbcfg.db_host}:{dbcfg.db_port}/{dbcfg.db_database}'
    )
    log.info('mysql is running!')
    DATABASE_URL = f'mysql+pymysql://{dbcfg.db_username}:{urlquote(dbcfg.db_password)}@{dbcfg.db_host}:{dbcfg.db_port}/{dbcfg.db_database}?charset=utf8'

log.info(DATABASE_URL)
engine = create_engine(DATABASE_URL, echo=echo_enable)
async_engine = create_async_engine(
    ASYNC_SQLALCHEMY_DATABASE_URL,
    echo=dbcfg.db_echo,
    max_overflow=dbcfg.db_max_overflow,
    pool_size=dbcfg.db_pool_size,
    pool_recycle=dbcfg.db_pool_recycle,
    pool_timeout=dbcfg.db_pool_timeout,
)

AsyncSessionLocal = async_sessionmaker(autocommit=False, autoflush=False, bind=async_engine)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine, expire_on_commit=True)

class Base(AsyncAttrs, DeclarativeBase):
    pass


def advanced_camel_to_snake(name: str) -> str:
    """Convert camelCase/PascalCase to snake_case (handles acronyms like 'XMLHttpRequest')"""
    return re.sub(r'([a-z0-9])([A-Z])', r'\1_\2', name).lower()
def snake_to_camel(snake_str: str) -> str:
    """Convert snake_case to lowerCamelCase"""
    components = snake_str.split('_')
    return components[0] + ''.join(x.title() for x in components[1:])

class AutoSnakedBase(Base):
    __abstract__ = True
    # 2. Auto-convert column names (camelCase → snake_case)
    @declared_attr
    def __mapper_args__(cls):
        return {'column_prefix': '_'}  # Optional safety prefix

    # 3. Dynamic property access (snake_case → camelCase)
    def __getattr__(self, camel_name):
        snake_name = snake_to_camel(camel_name)
        if snake_name in self.__table__.columns:
            return getattr(self, snake_name)
        raise AttributeError(f"No attribute {camel_name}")


def get_model_by_table_name(table_name: str) -> Optional[Base]:
    """
    通过表名查找对应的 ORM 模型类
    :param table_name: 表名
    :return: 模型类（未找到则返回 None）
    """
    for mapper in Base.registry.mappers:
        if mapper.persist_selectable.name == table_name:
            return mapper.class_
    return None